#!/usr/bin/env python3

import tkinter
import tkinter.messagebox
from tkinter import filedialog
import os
import shutil
import xlrd
import xlwt
from xlutils.copy import copy

class SelectFiles(object):
    """
    选择文件。
    选择一个目录，读取其中的所有doc或docx文档，检索其中的表格字段，得到姓名。处理选择的Excel文件，对其进行处理。
    """

    def __init__(self):
        # 创建主窗口，用于容纳其他组件
        self.root = tkinter.Tk()
        self.root.geometry("620x300")
        # 给主窗口设置标题
        self.root.title("花名册统计处理小工具 v0.1 macl")

        # 创建一个label，一个编辑框，一个按钮
        self.dirpath_label = tkinter.Label(self.root, text="doc文件目录：")
        self.dirpath_edit = tkinter.Entry(self.root, width=68)
        self.sel_dirpath_button = tkinter.Button(self.root, command=self.select_dir_path, text="...")

        # 创建一个label，一个编辑框，一个按钮
        self.txtfilepath_label = tkinter.Label(self.root, text="txt姓名列表文件：")
        self.txtfilepath_edit = tkinter.Entry(self.root, width=68)
        self.sel_txtfilepath_button = tkinter.Button(self.root, command=self.select_txt_file_path, text="...")

        # 创建一个label，一个编辑框，一个按钮
        self.filepath_label = tkinter.Label(self.root, text="Excel文件：")
        self.filepath_edit = tkinter.Entry(self.root, width=68)
        self.sel_filepath_button = tkinter.Button(self.root, command=self.select_file_path, text="...")

        # 创建按钮
        self.run_button = tkinter.Button(self.root, width=15, command=self.run_select_files, text="开始处理")

    # 完成界面布局
    def gui_arrange(self):
        nrow = 0
        self.dirpath_label.grid(row=nrow, column=0)
        self.dirpath_edit.grid(row=nrow, column=1)
        self.sel_dirpath_button.grid(row=nrow, column=2)

        nrow += 1
        self.txtfilepath_label.grid(row=nrow, column=0)
        self.txtfilepath_edit.grid(row=nrow, column=1)
        self.sel_txtfilepath_button.grid(row=nrow, column=2)

        nrow += 1
        self.filepath_label.grid(row=nrow, column=0)
        self.filepath_edit.grid(row=nrow, column=1)
        self.sel_filepath_button.grid(row=nrow, column=2)

        nrow += 1
        self.run_button.grid(row=nrow, column=1)

    # 选择文件夹路径
    def select_dir_path(self):
        dirpath = filedialog.askdirectory(title="选择要处理的doc文件夹目录")
        self.dirpath_edit.delete(0, tkinter.END)
        self.dirpath_edit.insert(0, dirpath)
    
    # 选择txt姓名列表文件路径
    def select_txt_file_path(self):
        filepath = filedialog.askopenfilename(title="选择要处理的人员姓名列表txt文件", filetypes=[('txt files', '*.txt'), ('All files', '*')])
        self.txtfilepath_edit.delete(0, tkinter.END)
        self.txtfilepath_edit.insert(0, filepath)
    
    # 选择列表文件路径
    def select_file_path(self):
        filepath = filedialog.askopenfilename(title="选择人员花名册Excel文档", filetypes=[('Excel files', '*.xls'), ('All files', '*')])
        self.filepath_edit.delete(0, tkinter.END)
        self.filepath_edit.insert(0, filepath)

    # 获取sheet中具体表格的字体颜色
    def get_cell_text_color(self, rb, rs, row, col):
        xfx = rs.cell_xf_index(row, col)
        ft = rb.xf_list[xfx]
        font = rb.font_list[ft.font_index]
        color = rb.colour_map[font.colour_index]
        return color
    
    # 执行选择文件操作
    def run_select_files(self):
        dirpath = self.dirpath_edit.get()
        txtfilepath = self.txtfilepath_edit.get()
        filepath = self.filepath_edit.get()

        namelist = []
        if dirpath != "": # 选择目录
            new_file_path = os.path.join(dirpath, "../姓名列表--标红处理的.txt")
            with open(new_file_path, "w", encoding="UTF-8") as f:
                for file in os.listdir(dirpath):
                    namelist.append(file.split('.')[0])
                    f.write(file.split('.')[0])
                    f.write('\n')
            f.close()
        else: # 选择txt文件
            with open(txtfilepath, "r", encoding="UTF-8") as f:
                namelist.append(f.readline())
            f.close()

        rb = xlrd.open_workbook(filepath, formatting_info=True)
        rs = rb.sheet_by_index(0)
        nrows = rs.nrows
        ncols = rs.ncols

        wb = xlwt.Workbook()
        ws = wb.add_sheet(rs.name)
        filepath_modify = "{0}-处理后.xls".format(filepath.split('.')[0])

        borders = xlwt.Borders()
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN

        # 标红处理的
        font = xlwt.Font()
        font.name = "微软雅黑"
        font.colour_index = 10
        font.height = 20*11 # 字体大小，12为字号，20为衡量单位

        style = xlwt.XFStyle()
        style.font = font
        style.borders = borders

        datastyle1 = xlwt.XFStyle()
        datastyle1.num_format_str = 'yyyy/mm/dd'
        datastyle1.borders = borders
        datastyle1.font = font

        datastyle2 = xlwt.XFStyle()
        datastyle2.num_format_str = 'yyyy年mm月'
        datastyle2.borders = borders
        datastyle2.font = font

        # 不进行处理的
        font_undo = xlwt.Font()
        font_undo.name = "微软雅黑"
        font_undo.height = 20*10

        style_undo = xlwt.XFStyle()
        style_undo.borders = borders
        style_undo.font = font_undo

        datastyle1_undo = xlwt.XFStyle()
        datastyle1_undo.num_format_str = 'yyyy/mm/dd'
        datastyle1_undo.borders = borders
        datastyle1_undo.font = font_undo

        datastyle2_undo = xlwt.XFStyle()
        datastyle2_undo.num_format_str = 'yyyy年mm月'
        datastyle2_undo.borders = borders
        datastyle2_undo.font = font_undo

        undo_namelist = []
        for i in range(nrows):
            name = rs.cell(i,0).value
            color = self.get_cell_text_color(rb, rs, i, 0)
            if color == (255, 0, 0) or (name in namelist): # 红色 或者 需要处理
                for j in range(ncols):
                    if j==4 or j==5 or j==11: 
                        ws.write(i, j, rs.cell(i,j).value, datastyle1)
                    elif j==12:
                        ws.write(i, j, rs.cell(i,j).value, datastyle2)
                    else:
                        ws.write(i, j, rs.cell(i,j).value, style)
            else:
                if name != '姓名':
                    undo_namelist.append(name)
                for j in range(ncols):
                    if j==4 or j==5 or j==11: 
                        ws.write(i, j, rs.cell(i,j).value, datastyle1_undo)
                    elif j==12:
                        ws.write(i, j, rs.cell(i,j).value, datastyle2_undo)
                    else:
                        ws.write(i, j, rs.cell(i,j).value, style_undo)
        wb.save(filepath_modify)

        undo_file_path = os.path.join(dirpath, "../姓名列表--剩余未处理的.txt")
        with open(undo_file_path, "w", encoding="UTF-8") as f:
            for name in undo_namelist:
                f.write(name)
                f.write('\n')
        f.close()

        tkinter.messagebox.showinfo('提示', '处理完成！')


def main():
    # 初始化对象
    SF = SelectFiles()
    # 界面布局
    SF.gui_arrange()
    # 主程序执行
    tkinter.mainloop()
    pass


if __name__ == "__main__":
    main()

